package com.learning.platform.repository;

import com.learning.platform.entity.ExamRecord;
import com.learning.platform.enums.ExamRecordStatus;
import com.learning.platform.enums.PassStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface ExamRecordRepository extends JpaRepository<ExamRecord, Long> {

    List<ExamRecord> findByUserIdAndExamIdOrderByAttemptNumberDesc(Long userId, Long examId);

    Optional<ExamRecord> findFirstByUserIdAndExamIdOrderByAttemptNumberDesc(Long userId, Long examId);

    Page<ExamRecord> findByUserIdOrderByCreatedAtDesc(Long userId, Pageable pageable);

    default Page<ExamRecord> findByUserId(Long userId, Pageable pageable) {
        return findByUserIdOrderByCreatedAtDesc(userId, pageable);
    }

    Page<ExamRecord> findByExamIdOrderByCreatedAtDesc(Long examId, Pageable pageable);

    long countByUserIdAndExamId(Long userId, Long examId);

    @Query("SELECT COUNT(DISTINCT er.userId) FROM ExamRecord er WHERE er.examId = :examId")
    long countDistinctUsersByExamId(@Param("examId") Long examId);

    long countByExamId(Long examId);

    @Query("SELECT COUNT(er) FROM ExamRecord er WHERE er.examId = :examId AND er.passStatus = :passStatus")
    long countByExamIdAndPassStatus(@Param("examId") Long examId, @Param("passStatus") PassStatus passStatus);

    @Query("SELECT AVG(er.totalScore) FROM ExamRecord er WHERE er.examId = :examId AND er.status = 'SUBMITTED'")
    Double getAverageScoreByExamId(@Param("examId") Long examId);

    List<ExamRecord> findByUserIdAndStatusOrderByStartTimeDesc(Long userId, ExamRecordStatus status);

    default Optional<ExamRecord> findInProgressRecord(Long userId, Long examId) {
        return findByUserIdAndExamIdOrderByAttemptNumberDesc(userId, examId)
                .stream()
                .filter(record -> record.getStatus() == ExamRecordStatus.IN_PROGRESS)
                .findFirst();
    }

    @Query("SELECT er FROM ExamRecord er WHERE er.userId = :userId AND er.examId = :examId " +
           "AND er.status = 'SUBMITTED' ORDER BY er.totalScore DESC")
    List<ExamRecord> findByUserIdAndExamIdOrderByTotalScoreDesc(@Param("userId") Long userId, @Param("examId") Long examId);

    @Query(value = "SELECT DATE(created_at) as day, " +
                   "COUNT(*) as recordCount, " +
                   "AVG(total_score) as avgScore, " +
                   "ROUND(COUNT(CASE WHEN pass_status = 'PASSED' THEN 1 END) * 1.0 / COUNT(*), 4) as passRate " +
                   "FROM exam_records " +
                   "WHERE exam_id = :examId AND created_at >= DATE_SUB(NOW(), INTERVAL :days DAY) " +
                   "GROUP BY DATE(created_at) " +
                   "ORDER BY day DESC", nativeQuery = true)
    List<DailyTrendRow> findDailyTrend(@Param("examId") Long examId, @Param("days") int days);

    void deleteByUserId(Long userId);
}